Reference Manual for Flight Planning Excel Spreadsheet Sriram Narayan Rev 0.4 Introduction This Excel flight planning spreadsheet runs on Excel versions greater than 5.0a. The spreadsheet has been geared towards Cessna 152 and Cessna 172 aircraft, however, it can be modified to suit other aircraft quite easily. The spreadsheet consists of 4 sheets. The first sheet is the “Flt Plan” sheet which consists of the wind based time/speed/fuel calculations for the various VFR checkpoints the user wishes to input. It also contains various miscellaneous information that may be gimmicky but nevertheless interesting data that will hopefully not detract from the utility of the main flight-planning section. The second sheet is the “Gen Info” section which has navigation information, comm/nav frequencies and airport information and “sketches” the user may wish to have in hand when approaching the destination. The third sheet, added in this revision, is the Weather sheet. Lastly, there is the “W&B” section on weight and balance, that will allow the user to determine with the help of a graph whether the plane meets the CG and moment requirements. The next few sections will delve into each of these topics further. Flt-Plan Section The user needs to input data into sections that are shown in red in the example spreadsheet. The blue text areas which have a grey background are calculated fields that are protected when the sheet is protected. Black text is usually headings. Green text is usually non-printed and calculation fields. Magenta text are option descriptors that describe a specific option for a nearby field. The Location heading is the VFR checkpoints that the user needs to identify. The first one (A5) is the origin airport and is entered as the 3 or 4 letter identifier. The next row is the “Top of Climb” whose information is not to be entered, instead the user must enter the first VFR checkpoint at A7. The distance to the first checkpoint is entered at the bottom of all the checkpoints at K21. The reason for this is that the program determines the top of climb (TOC) distance and times based on final altitude and airspeeds. It then assigns the remaining distance and time to the first checkpoint from the TOC position which can vary depending on a lot of factors as you know. One caution is that if the first checkpoint occurs before TOC is reached, there may be some negative numbers in some of the fields! Other intermediate checkpoints are entered till the final destination airport where you enter 3 or 4 letter airport identifier. The other headings that need data to be entered into are wind direction, WndD, wind velocity (WndV) given as true direction and true airspeed (TAS). In the spreadsheet, I have used values of 95 knots for Cessna 152s and 110 knots for Cessna 172s in cruise. The Vy speeds during climb-outs are 65 and 70 knots respectively. The program automatically switches between the two aircraft based on the P30 field value. Strictly speaking, the TAS field can then be made into a “blue” field if you decide to use this switching option. However, you may need to adjust the speeds for your particular aircraft. The true course (TC) is entered in column F and distance between checkpoints in column K. The magnetic variation is entered in field B26 and this is used to calculate the true heading (TH) and the spreadsheet figures out the wind correction angle (WCA), magnetic course (MC), magnetic heading (MH), fuel consumption for each leg (Fuel). Note that the MC should be used to determine VFR flying altitudes as per the hemispherical rule. There is also a ground speed (GS) calculation and two time columns, estimated time enroute (ETE) and estimated time of arrival (ETA). Blank spaces are provided for the actual times that may be entered during the flight. There is an extra (Contact/Notes) blank field that is provided for the pilot to put in contact frequencies or other notes associated with each checkpoint. In order to customise the sheet like adding more rows, the recommendation is to copy an entire row (that already exists) so that all the formulas that are hidden and other non- printed columns that hold calculations are also properly copied. If this is not done, errors may result. IMPORTANT: In the fuel consumption portion, I have taken the liberty of putting a 40% higher fuel consumption during climb-out and a 35% reduction in fuel consumption during descent. These two fields are underlined in the spreadsheet. The user may wish to adjust these percentages if he/she is not comfortable with this. Also please be aware that the fuel consumption rate I have used for the Cessna 152 is 6 gph and for Cessna 172 is 8 gph. Other data that can be entered into this sheet include latitude and longitude of the origin and destination airports. The program will calculate the great circle distance in field B27. Hopefully this distance will be less than the total distance arrived at in field K24! Date and time information can be entered which is used for other calculations such as whether night flight will be encountered. If you take passengers, based on the last time you flew, the sheet will check if you are current for that. You will also need to put in the amount of fuel you started the flight with in field L29, the program will then check if you have sufficient fuel when you complete the flight. The method of determining how much fuel is acceptable at the end of the flight can be altered by the user depending on level of comfort and FAA requirements. In the program currently it will warn you if you fall below a quarter tank. This is overly conservative but OK. You can enter actual temperature at the departing airport and program will calculate density altitude based on departing airport field elevation. Actual sunset times if entered correctly will allow you stay legal for night flight. I have not bothered with sunrise times. The program can also calculate the entire spreadsheet in Zulu time if you so desire and there are some fields at the bottom left which deal with time conversions. The spreadsheet at this time does not know when Daylight Savings takes place. One last feature that is available on this spreadsheet is to be able to predict the cost of the trip based on $/hr for the C152 and C172. You can use this to do a what-if comparison to see which aircraft turns out to be more economical as the C152 has lower speeds resulting in longer flight time as opposed to the faster but more expensive C172. Gen Info Section This is a more free form sheet that contains airport information, navaid information, approach and departure frequencies, airport comm frequencies, telephone numbers etc. and the user has considerable latitude in modifying this sheet to suit his/her requirements. I have also put in a stick-diagram for the runways at the destination airport as well as some pattern and runway length information. There is room to put notes and other information as well. W&B Section This gives a graphical view of the weight and balance situation for either the C152 or C172 that you have chosen. It uses fuel information from the flight planning section, but the user will have to enter the pilot and passenger weights, luggage etc. The W&B will work for the aircraft type specified be it the C152 or the C172 in the Normal category only. Utility category is not covered. The numbers have been taken the POH of the C152 and C172 and the user will need to change the Basic Empty weight for the particular C152 or C172. Note, the data for the rear passengers is not used when the aircraft specified is a C152 even though it may have a value in that field. Weather Section This recently added sheet (ver 0.4) has some fields where the pilot can enter the weather data from DUATs or a live briefing. Also there is some space to write down NOTAMs and ATIS information. As a reference, I have added some of the common METAR/TAF weather phenomena abbreviations. Conclusions I have tried to describe the basic workings of the spreadsheet that has evolved over the years. I think it is reasonably stable at this point in time but I hope to continue to improve it. Most of the basics are already there and I personally feel quite comfortable with the interface and final hard-copy that I can take with me on the flight. One thing I would like to add is an automatic course reversal sheet using the information from the main flight planning sheet. I also would like to add some calculations to warn the pilot if density altitude could be a problem based on runway length and ambient temperature. If you have any other suggestions on improving this spreadsheet please contact me via e-mail. If you find any errors I would appreciate your immediate feedback. Finally, some disclaimers - USE AT YOUR OWN RISK!!! I cannot guarantee the accuracy of the spreadsheet and strongly recommend that the data provided from this spreadsheet be supplemented with your own calculations and good judgment as PIC especially with regards to fuel estimates. I have verified the flight-planning results with an E6B and the agreement was good for the level of accuracy involved with mechanical devices. Thank-you and wish you safe landings…. If you find the application useful and wish to modify it and post your own version of it, please feel free to do so. I do request you though to point a reference to me either on your download page or within the application. Thank-you. Revision History Ver 0.3 First Released version (to the internet) Date: 17.11.1996 Ver 0.4 Added magnetic course and Notes fields in Flt-plan section and added the Weather sheet. Date: 3.2.1997. Acknowledgments Thanks to Gene Whitt and Gerald Kurata for their comments and critiquing the spreadsheet. Sriram Narayan --------------------------------------------------------------------------------------------------- File information /Author Information e-mail: sriram.narayan@technologist.com URL: http://www.dsp.net/narayan/snfltpl4.zip (this app) URL: http://www.dsp.net/narayan (my homepage) The files contained in the zip archive (snfltpl4.zip) are: snfltpl4.xls Excel 5.0a spreadsheet snfltpl4.doc Word 6.0 document snfltpl4.txt Plain text version of above. The above files and the zip archive can also be retrieved through anonymous ftp from ftp://ftp.dsp.com/users/narayan 4 VFR Flight planner version 0.4 Sriram Narayan February 3, 1997